package no.ntnu.fp.server;

/*
 * Changes not yet commited.
 * Wtf is this shit!?
 */
import java.util.List;
import java.security.acl.LastOwnerException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.Set;

import no.ntnu.fp.model.Appointment.AppointmentType;
import no.ntnu.fp.model.Appointment.RSVP;
import no.ntnu.fp.model.Message;
import no.ntnu.fp.model.Appointment;
import no.ntnu.fp.model.Message.MessageType;
import no.ntnu.fp.model.Person;
import no.ntnu.fp.model.Room;

public class DBConnection {

	private Connection connection = null;

	private String userName = "esbena_fp23";
	private String password = "thomas";
	private String serverName = "mysql.stud.ntnu.no";
	private String dbms = "mysql";
	private String portNumber; // DONNO
	private String dbName = "esbena_felles";

	private PreparedStatement updateAppointment;
	private PreparedStatement updateFlagOnAppointment;
	private PreparedStatement updateParticipateField;
	private PreparedStatement updateAllFieldsMessage;
	private PreparedStatement updateAllFieldsAppointment;

	private PreparedStatement selectPersonByUsername;
	private PreparedStatement selectPersonByName;
	private PreparedStatement selectAppointment;
	private PreparedStatement selectRoom;
	private PreparedStatement selectMessagesByUser;
	private PreparedStatement selectNextAppointment;
	private PreparedStatement selectLastAppointmentID;
	private PreparedStatement selectPassword; // Usikker pï¿½ hvordan returen
												// skal handles.
	private PreparedStatement selectAvailableRoom;
	private PreparedStatement selectAppointmentByPersonId;
	private PreparedStatement selectPersonByPersonId;

	private PreparedStatement insertAppointment;
	private PreparedStatement insertMessage;
	private PreparedStatement insertAppointmentToParticipants;
	private PreparedStatement insertAppointmentToUser;
	
	private PreparedStatement deleteFromAppointmentToUser;

	public DBConnection() {

		try {
			initializeConnection();
			updateAppointment = connection
					.prepareStatement("UPDATE Appointment SET ?=? WHERE AppointmentId=?;");
			updateAllFieldsAppointment = connection
					.prepareStatement("UPDATE Appointment SET Title = ?, AppointmentType = ?, StartDate = ?, EndDate = ?, StartTime = ?, EndTime = ?, Description = ?, ExternalParticipants = ?, AliveFlag = ?, Place = ? WHERE AppointmentId = ?;");
			// "CREATE TABLE Message(MessageId INT AUTO_INCREMENT, PersonId INT
			// NOT NULL, AppointmentId INT(32) UNSIGNED, MessageType INT
			updateAllFieldsMessage = connection
					.prepareStatement("UPDATE Message SET PersonId = ?, AppointmentId = ?, MessageType = ?, Active = ? WHERE MessageId = ?");
			updateFlagOnAppointment = connection
					.prepareStatement("UPDATE Appointment SET AliveFlag = false WHERE AppointmentId = ?;");
			updateParticipateField = connection
					.prepareStatement("UPDATE AppointmentToUser SET Participate = ? WHERE AppointmentId = ? AND PersonId = ?;");
			/*
			 * Select statements
			 */

			selectPersonByUsername = connection
					.prepareStatement("SELECT * FROM Person WHERE Username=?;");
			selectPersonByName = connection
					.prepareStatement("SELECT * FROM Person WHERE Name=?%;");
			selectAppointment = connection
					.prepareStatement("SELECT * FROM Appointment WHERE AppointmentId=?;");
			selectRoom = connection
					.prepareStatement("SELECT RoomId FROM Room WHERE Capacity>=? ORDER BY Capacity ASC;");
			selectMessagesByUser = connection
					.prepareStatement("SELECT * FROM Message WHERE Person=?;");
			selectNextAppointment = connection
					.prepareStatement("SELECT * FROM Appointment WHERE StartDate = ? AND StartTime = ? AND AliveFlag = true;");
			selectAvailableRoom = connection.prepareStatement("SELECT RoomId "
					+ "FROM Appointment AS A, Room AS R"
					+ "LEFT JOIN A ON A.RoomId = R.RoomId "
					+ "WHERE R.Capacity > ?"
					+ "AND ((A.StartDate IS NULL AND A.EndDate IS NULL "
					+ "OR (A.StartDate < ? AND A.EndDate < ?) "
					+ "OR (A.StartDate > ? AND A.EndDate > ?))"
					+ "AND ((A.StartTime < ? AND A.EndTime < ?) "
					+ "OR (A.StartTime > ? AND A.EndTime > ?);");

			selectAppointmentByPersonId = connection
					.prepareStatement("SELECT (A.Title, A.AppointmentType, A.StartDate, A.StartTime, A.EndDate, A.EndTime, A.AliveFlag, A.AppointmentId, ATU.PersonId) "
							+ "FROM Appointment AS A, AppointmentToUser AS ATU"
							+ "WHERE ATU.PersonId = ? "
							+ "AND ATU.AppointmentId = A.AppointmentId;");

			selectLastAppointmentID = connection
					.prepareStatement("SELECT MAX(AppointmentId) FROM Appointment;");
			selectPassword = connection
					.prepareStatement("SELECT Password FROM Person WHERE Username = ?");
			selectPersonByPersonId = connection
					.prepareStatement("SELECT * FROM Person WHERE PersonId = ?");
			/*
			 * Insert statements
			 */
			insertAppointment = connection
					.prepareStatement("INSERT INTO Appointment(Title, AppointmentType, StartDate, EndDate, StartTime, EndTime"
							+ ", Description, ExternalParticipants, AliveFlag, Place) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
			insertMessage = connection
					.prepareStatement("INSERT INTO Message(PersonId, AppointmentId, MessageType) VALUES(?, ?, ?);");
			insertAppointmentToParticipants = connection
					.prepareStatement("INSERT INTO AppointmentToUser VALUES (?, ?, ?, ?);");
			insertAppointmentToUser = connection
					.prepareStatement("INSERT INTO AppointmentToUser VALUES (?, ?, false, ?);");
			
			deleteFromAppointmentToUser = connection
					.prepareStatement("DELETE FROM AppointmentToUser WHERE AppointmentID=?" +
							"AND Administrator=false;");

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public void updateAllParticipate(Appointment a) {
		
		for (Entry<Integer, RSVP> p : a.inviteList.entrySet()) {
			
			updateParticipateField(p.getValue().ordinal(), a.id, p.getKey());
		}
	}

	// updateAllFieldsMessage = connection.prepareStatement("UPDATE Message SET
	// PersonId = ?,
	// AppointmentId = ?, MessageType = ? WHERE MessageId = ?");
	public void updateMessageAllFields(Message m) {
		
		try {
			updateAllFieldsMessage.setInt(1, m.to);
			updateAllFieldsMessage.setInt(2, m.appointment);
			updateAllFieldsMessage.setInt(3, m.messageType.ordinal());
			updateAllFieldsMessage.setBoolean(4, m.active);
			updateAllFieldsMessage.setInt(5, m.id);
			updateAllFieldsMessage.execute();

		} catch (SQLException e) {
		e.printStackTrace();
		}
	}

	public void updateAppointment(Appointment a) {
		
		try {
			updateAllFieldsAppointment.setString(1, a.title);
			updateAllFieldsAppointment.setInt(2, a.type.ordinal());
			updateAllFieldsAppointment.setDate(3, a.startDate);
			updateAllFieldsAppointment.setDate(4, a.endDate);
			updateAllFieldsAppointment.setTime(5, a.start);
			updateAllFieldsAppointment.setTime(6, a.end);
			updateAllFieldsAppointment.setString(7, a.place);
			updateAllFieldsAppointment.setInt(8, a.guestCount);
			updateAllFieldsAppointment.setBoolean(9, a.alive);
			updateAllFieldsAppointment.setString(10, a.place);
			updateAllFieldsAppointment.setInt(11, a.id);

			updateAllFieldsAppointment.execute();
			/*
			for (int i : a.inviteList.keySet()) {
				if (a.alive)
					insertMessage(i, a.id, MessageType.MEETING_CHANGED.ordinal());
				else
					insertMessage(i, a.id, MessageType.MEETING_CANCELED.ordinal());
			}
			*/
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void selectPersonByPersonId(int personId) {
		ResultSet rs = null;
		Map<String, Object> person = new HashMap<String, Object>();

		try {
			selectPersonByPersonId.setInt(1, personId);
			rs = selectPersonByPersonId.executeQuery();

			if (rs.first()) {
				person.put("name", rs.getString("Name"));
				person.put("username", rs.getString("Username"));
				person.put("email", rs.getString("Email"));
				person.put("id", rs.getInt("PersonId"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Selects available rooms
	 * 
	 * @param cap
	 *            int capacity
	 * @param sd
	 *            Date StartDate
	 * @param ed
	 *            Date EndDate
	 * @param st
	 *            Time StartTime
	 * @param et
	 *            Time EndTime
	 */
	public void selectAvailableRoom(int cap, Date sd, Date ed, Time st, Time et) {
		ResultSet rs = null;
		Map<String, Object> available = new HashMap<String, Object>();
		Room r = new Room();
		try {
			selectAvailableRoom.setInt(1, cap);
			selectAvailableRoom.setDate(2, sd);
			selectAvailableRoom.setDate(3, ed);
			selectAvailableRoom.setDate(4, sd);
			selectAvailableRoom.setDate(5, ed);
			selectAvailableRoom.setTime(6, st);
			selectAvailableRoom.setTime(7, et);
			selectAvailableRoom.setTime(8, st);
			selectAvailableRoom.setTime(9, et);

			rs = selectAvailableRoom.executeQuery();
			while (rs.next()) {
				available.put("id", rs.getInt("RoomId"));
				available.put("capacity", rs.getInt("Capacity"));

				r.unpack(available);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Method to collect information about other users calendars
	 * 
	 * @param PersonId
	 */
	public void selectAppointmentByPersonId(int PersonId) {
		Map<String, Object> apps = new HashMap<String, Object>();
		Appointment ap;
		ResultSet rs = null;

		try {
			selectAppointmentByPersonId.setInt(1, PersonId);
			rs = selectAppointment.executeQuery();
			while (rs.next()) {
				ap = new Appointment();
				apps.put("owner", rs.getInt("A.PersonId"));
				apps.put("startTime", rs.getTime("A.StartTime"));
				apps.put("startDate", rs.getDate("A.StartDate"));
				apps.put("endTime", rs.getTime("A.EndTime"));
				apps.put("endDate", rs.getDate("A.EndDate"));
				apps.put("title", rs.getString("A.Title"));
				apps.put("type", rs.getString("A.AppointmentType"));
				apps.put("alive", rs.getBoolean("A.AliveFlag"));
				apps.put("id", rs.getInt("A.AppointmentId"));
				apps.put("room", rs.getInt("A.RoomId"));
				ap.unpack(apps);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Collects password from database.
	 * 
	 * @param user
	 * @return String wich contains password collected from database
	 */
	/*
	 * TODO:Must check username before?
	 */
	public String selectPassword(String user) {
		ResultSet rs = null;
		String pass = null;
		try {
			selectPassword.setString(1, user);
			rs = selectPassword.executeQuery();
			if (rs.first()) {
				pass = rs.getString("Password");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return pass;
	}

	/**
	 * Method for users to change their participate-field
	 * 
	 * @param participateValue
	 * @param appId
	 * @param username
	 */
	public void updateParticipateField(int participateValue, int appId,
			int PersonId) {
		try {
			updateParticipateField.setInt(1, participateValue);
			updateParticipateField.setInt(2, appId);
			updateParticipateField.setInt(3, PersonId);
			updateParticipateField.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Method to update flag on appointment. Only Admin can use this method
	 * 
	 * @param appId
	 */
	public void updateFlagOnAppointment(int appId) {
		try {
			updateFlagOnAppointment.setInt(1, appId);
			updateFlagOnAppointment.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Method for inserting a message. This method should be autogenerated from
	 * the Appointmentmodel.
	 * 
	 * @param username
	 * @param appid
	 * @param type
	 */
	public void insertMessage(int personId, int appid, int type) {
		
			try {
				insertMessage.setInt(1, personId);
				insertMessage.setInt(2, selectLastAppointmentID());
				insertMessage.setInt(3, type);
				insertMessage.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		

	}

	/**
	 * Used because appointmentId Auto_increments, so we can easy find the last
	 * appointment when we register participants in database
	 * 
	 * @return lastAppointmentId
	 */
	private int selectLastAppointmentID() {
		int lastId = -1;
		ResultSet rs = null;
		try {
			rs = selectLastAppointmentID.executeQuery();
			if (rs.first()) {
				//lastID = rs.getInt("AppointmentId");
				lastId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return lastId;
	}
	
	public void insertAppointmentToUser(int aId, int pId, RSVP r) {
		
		try {
			insertAppointmentToUser.setInt(1, aId);
			insertAppointmentToUser.setInt(2, pId);
			insertAppointmentToUser.setInt(3, r.ordinal());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		
	}

	/**
	 * Inserts the (potential) appointment to participants.
	 * 
	 * @param username
	 * @param admin
	 */
	private void insertAppointmentToParticipants(int PersonId, boolean admin) {
		int thisAppointmentId = selectLastAppointmentID();
		try {
			insertAppointmentToParticipants.setInt(1, PersonId);
			insertAppointmentToParticipants.setInt(2, thisAppointmentId);
			insertAppointmentToParticipants.setBoolean(3, admin);
			insertAppointmentToParticipants.setInt(4, 0);
			insertAppointmentToParticipants.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Method called when the Admin of the appointment presses the
	 * "Confirm"-button.
	 * 
	 * @param tittel
	 * @param type
	 * @param startDate
	 * @param endDate
	 * @param startTime
	 * @param endTime
	 * @param desc
	 * @param external
	 * @param alive
	 * @param place
	 * @param participantsByUsername
	 * @param administrator
	 */
	public void insertAppointment(String tittel, int type, Date startDate,
			Date endDate, Time startTime, Time endTime, String desc,
			int external, boolean alive, String place,
			Set<Integer> participantsByPersonId, int administrator) {
		try {
			insertAppointment.setString(1, tittel);
			insertAppointment.setInt(2, type);
			insertAppointment.setDate(3, startDate);
			insertAppointment.setDate(4, endDate);
			insertAppointment.setTime(5, startTime);
			insertAppointment.setTime(6, endTime);
			insertAppointment.setString(7, desc);
			insertAppointment.setInt(8, external);
			insertAppointment.setBoolean(9, alive);
			insertAppointment.setString(10, place);
			insertAppointment.execute();
			
			for (int s : participantsByPersonId) {
			//	insertMessage(s, selectLastAppointmentID(), 
			//			MessageType.INVITE.ordinal());
				insertAppointmentToParticipants(s, s == administrator);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Method to keep track of a person's next appointment
	 * 
	 * @param startDate
	 * @param startTid
	 */
	public void selectNextAppointment(Date startDate, Time startTid) {
		Appointment nextAppo = null;
		ResultSet rs = null;
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			selectNextAppointment.setDate(1, startDate);
			selectNextAppointment.setTime(2, startTid);
			rs = selectNextAppointment.executeQuery();
			if (rs.first()) {
				nextAppo = new Appointment();

				map.put("room", rs.getInt("RoomId"));
				map.put("startDate", rs.getDate("startDate"));
				map.put("endDate", rs.getDate("endDate"));
				map.put("startTime", rs.getTime("startTime"));
				map.put("endTime", rs.getTime("endTime"));
				map.put("description", rs.getString("Description"));
				map.put("guestCount", rs.getInt("ExternalParticipants"));
				map.put("type", rs.getString("AppointmentType"));
				map.put("appointmentId", rs.getInt("AppointmentId"));

				nextAppo.unpack(map);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Retrieves messages. Model-layer must ensure that messages can only be
	 * read by the user who "owns" the message. Sort of a inbox
	 * 
	 * @param username
	 */
	public void selectMessagesByUser(int PersonId) {
		ResultSet rs = null;
		Map<String, Object> messages = new HashMap<String, Object>();
		Message mess = new Message();
		try {
			selectMessagesByUser.setInt(1, PersonId);
			rs = selectMessagesByUser.executeQuery();
			while (rs.next()) {

				messages.put("id", rs.getString("AppointmentId"));
				messages.put("active", rs.getString("AliveFlag"));
				mess.unpack(messages);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/*
	 * Select-methods become voids because of the architechture in models.
	 */
	public void selectRoom(int capacity) {
		Map<String, Object> rooms = new HashMap<String, Object>();

		ResultSet rs = null;
		// LinkedList<Room> rooms = new LinkedList<Room>();
		Room r = null;
		try {
			selectRoom.setInt(1, capacity);
			rs = selectRoom.executeQuery();
			while (rs.next()) {

				r = new Room();

				rooms.put("capacity", rs.getInt("Capacity"));
				rooms.put("name", rs.getString("Name"));
				r.unpack(rooms);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Method to update an appointment. Only admin should have rights to this.
	 * 
	 * @param attributeToSet
	 * @param valueToSet
	 * @param appointmentID
	 */
	public void updateAppointment(String attributeToSet, Object valueToSet,
			int appointmentID) {
		try {
			updateAppointment.setString(1, attributeToSet);
			updateAppointment.setObject(2, valueToSet);
			updateAppointment.setInt(3, appointmentID);
			updateAppointment.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Selects a specified Appointment
	 * 
	 * @param AppointmentId
	 */
	public void selectAppointment(int AppointmentId) {
		ResultSet rs = null;
		Appointment app = new Appointment();
		Map<String, Object> appointments = new HashMap<String, Object>();

		try {
			selectAppointment.setInt(1, AppointmentId);
			rs = selectAppointment.executeQuery();
			if (rs.first()) {
				appointments.put("id", rs.getInt("AppointmentId"));
				appointments.put("title", rs.getString("Title"));
				appointments.put("type", rs.getString("AppointmentType"));
				appointments.put("startDate", rs.getDate("startDate"));
				appointments.put("endDate", rs.getDate("endDate"));
				appointments.put("startTime", rs.getTime("startTime"));
				appointments.put("endTime", rs.getDate("endTime"));
				appointments.put("description", rs.getString("Description"));
				appointments.put("guestCount",
						rs.getInt("ExternalParticipants"));
				appointments.put("title", rs.getBoolean("AliveFlag"));
				appointments.put("place", rs.getString("Place")); // TODO
																	// Mangler
																	// 'place' i
																	// Appointment.
																	// Mï¿½
																	// ha
																	// stï¿½tte
																	// for
																	// personlige
																	// avtaler

				app.unpack(appointments);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * selects a person by name. Must be used in order to get the
	 * search-function to work
	 * 
	 * @param name
	 */
	public void selectPersonByName(String name) {
		ResultSet rs = null;
		Map<String, Object> possiblePersons = new HashMap<String, Object>();

		Person p = null;
		try {
			selectPersonByName.setString(1, name);
			rs = selectPersonByName.executeQuery();
			/*
			 * Trenger vi dateOfBirth i det hele tatt?
			 */

			while (rs.next()) {
				p = new Person();

				possiblePersons.put("name", rs.getString("Name"));
				possiblePersons.put("email", "Email");
				p.unpack(possiblePersons);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Selects a person by username
	 * 
	 * @param username
	 */
	public Person selectPersonByUsername(String username) {
		Person p = null;
		ResultSet rs = null;
		try {
			selectPersonByUsername.setString(1, username);
			rs = selectPersonByUsername.executeQuery();

			if (rs.first()) {
				p = new Person();
				p.id = rs.getInt("PersonId");
				p.name = rs.getString("Name");
				p.username = rs.getString("Username");
				p.email = rs.getString("Email");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}

	public void selectAll(String modelName) {

		ResultSet rs;
		PreparedStatement st;

		try {
			st = connection.prepareStatement("SELECT * FROM " + modelName + ";");
			rs = st.executeQuery();

			if (modelName.equals("Person")) {

				while (rs.next()) {
					Person p = new Person();
					p.id = rs.getInt("PersonId");
					p.username = rs.getString("Username");
					p.name = rs.getString("Name");
					p.email = rs.getString("Email");
					p.register(p.id);
				}
				System.out.println("Person initialized");
			} else if (modelName.equals("Appointment")) {

				while (rs.next()) {
					Appointment a = new Appointment();
					a.id = rs.getInt("AppointmentId");
					a.description = rs.getString("Description");
					a.title = rs.getString("Title");
					a.type = rs.getBoolean("AppointmentType") ? AppointmentType.PERSONAL
							: AppointmentType.WORK;
					a.alive = rs.getBoolean("AliveFlag");
					a.guestCount = rs.getInt("ExternalParticipants");
					a.startDate = rs.getDate("StartDate");
					a.endDate = rs.getDate("EndDate");
					a.start = rs.getTime("StartTime");
					a.end = rs.getTime("EndTime");
					a.place = rs.getString("Place");
					a.room = rs.getInt("RoomId");
					getInviteList(a);
					a.register(a.id);
				}
				System.out.println("Appointment initialized");
			} else if (modelName.equals("Message")) {

				while (rs.next()) {
					Message msg = new Message();
					msg.id = rs.getInt("MessageId");
					msg.messageType = Message.MessageType.values()[rs
							.getInt("MessageType")];
					msg.to = rs.getInt("PersonId");
					msg.appointment = rs.getInt("AppointmentId");
					msg.active = rs.getBoolean("Active");
					msg.register(msg.id);
				}
				System.out.println("Message initialized");
			} else if (modelName.equals("Room")) {

				while (rs.next()) {
					Room r = new Room();
					r.id = rs.getInt("RoomId");
					r.name = rs.getString("Name");
					r.capacity = rs.getInt("Capacity");
				}
				System.out.println("Room initialized");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
	}

	private void getInviteList(Appointment a) {
		PreparedStatement st = null;
		ResultSet rs = null;

		try {
			st = connection.prepareStatement("SELECT ATU.PersonId, ATU.Administrator, "
					+ "ATU.Participate FROM AppointmentToUser ATU WHERE AppointmentId=?");

			st.setInt(1, a.id);
			rs = st.executeQuery();
			while (rs.next()) {
				if (rs.getBoolean("Administrator")) {
					a.owner = rs.getInt("PersonId");
				} else {
					RSVP rsvp = RSVP.values()[rs.getInt("Participate")];
					int personId = rs.getInt("PersonId");
					a.inviteList.put(personId, rsvp);
				}
			}
			System.out.println("new owner: "+a.owner);
			rs.close();
		} catch (SQLException e) {
		e.printStackTrace();
		}
	}
	
	public void deleteFromAppointmentToUser(int id) {
		
		try {
			deleteFromAppointmentToUser.setInt(1, id);
			deleteFromAppointmentToUser.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

//	public static void main(String[] args) {
//
//		DBConnection d = new DBConnection();
//		long curr = System.currentTimeMillis();
//		String description = "Sexytiiiiiime";
//		List<Integer> deltar = new LinkedList<Integer>();
//		deltar.add(1);/*
//					 * d.insertAppointment("Jobbsex", 0, new Date(curr), new
//					 * Date(curr), new Time(curr), new Time(curr +1000000),
//					 * description, 2, true, "Puterom", deltar, 1);
//					 * 
//					 * 
//					 * d.insertAppointment("Sex med JÃ¸rgen", 0, new
//					 * Date(curr), new Date(curr), new Time(curr), new Time(curr
//					 * + 1500000), "Det blir sÃ¥ digg", 7, true,
//					 * "JÃ¸rgens bakgÃ¥rd", deltar, 1);
//					 */
//		System.out.println(d.selectPassword("esbena"));
//	}

	private void initializeConnection() throws SQLException {

		Properties connectionProps = new Properties();
		connectionProps.put("user", this.userName);
		connectionProps.put("password", this.password);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		if (this.dbms.equals("mysql")) {
			connection = DriverManager.getConnection("jdbc:" + this.dbms
					+ "://" + this.serverName + "/" + this.dbName,
					connectionProps);

		} else if (this.dbms.equals("derby")) {
			connection = DriverManager.getConnection("jdbc:" + this.dbms + ":"
					+ this.dbName + ";create=true", connectionProps);
		}
	}
}
